#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi

tmp_ods_product_all_log="
USE jtp_goods_warehouse;
DROP TABLE IF EXISTS jtp_goods_warehouse.tmp_ods_product_all_log;
CREATE TABLE IF NOT EXISTS jtp_goods_warehouse.tmp_ods_product_all_log
AS
SELECT
    log_id
     , product_id
     , sku_id
     , user_id
     , log_date
     , is_new
     , behavior_type
     , behavior_time
     , stay_duration
     , traffic_source
     , keyword
     , quantity
     , price
     , device_type
     , operation_type
     , session_id
     , ip
     , user_agent
     , price_star
     , competitor_id
     , competitor_price
     , product_click_count
     , fan_click_count
     , collect_guide_count
     , cart_guide_items
     , grass_purchase_amount
     , grass_purchase_users
     , keyword_type
     , exposure_uv
     , click_uv
     , content_id
     , content_type
     , play_pv
     , content_uv
     , gmv
     , main_product_id
     , related_product_id
     , cart_items
     , pay_buyer_count
     , pay_items
     , relation_type
     , relation_uv
     , relation_gmv
     , after_sale_id
     , after_sale_type
     , reason
     , create_time
     , status
FROM jtp_goods_warehouse.ods_product_all_log
WHERE dt = '${data_date}'
AND user_id <= 'u4000' AND product_id <= 'p4000'
;
"

tmp_dim_product_incr="
USE jtp_goods_warehouse;
DROP TABLE IF EXISTS jtp_goods_warehouse.tmp_dim_product_incr;
CREATE TABLE IF NOT EXISTS jtp_goods_warehouse.tmp_dim_product_incr
AS
SELECT
    user_id
     , gender
     , age
     , province
     , city
     , price_preference
     , interest_tags
     , sku_id
     , product_id
     , sku_name
     , attributes
     , price
     , stock
     , category_id
     , category_name
     , parent_category_id
     , level
     , brand_id
     , brand_name
     , brand_desc
     , review_id
     , rating
     , review_text
     , review_time
     , content_id
     , content_type
     , content_title
     , publish_time
FROM jtp_goods_warehouse.dim_total
WHERE dt = '${data_date}'
  AND sku_id IS NOT NULL
  AND category_id IS NOT NULL
  AND parent_category_id IS NOT NULL
  AND content_id IS NOT NULL
;
"


dwd_goods_info="
WITH t1 AS (
    SELECT
        t1.user_id
         , gender
         , age
         , price_preference
         , interest_tags
         , t1.sku_id
         , t1.product_id
         , sku_name
         , attributes
         , t1.price
         , stock
         , category_id
         , category_name
         , parent_category_id
         , level
         , brand_id
         , brand_name
         , brand_desc
         , review_id
         , rating
         , review_text
         , review_time
         , t1.content_id
         , t1.content_type
         , content_title
         , publish_time
         , behavior_type
         , stay_duration
         , traffic_source
         , keyword
         , quantity
         , device_type
         , operation_type
         , session_id
         , price_star
         , competitor_id
         , competitor_price
         , keyword_type
         , product_click_count
         , fan_click_count
         , collect_guide_count
         , cart_guide_items
         , grass_purchase_amount
         , grass_purchase_users
         , cart_items
         , pay_buyer_count
         , pay_items
         , relation_type
         , relation_uv
         ,row_number() over (PARTITION BY  t1.user_id,t1.sku_id ORDER BY review_time ) AS rk
    FROM jtp_goods_warehouse.tmp_dim_product_incr t1
             LEFT JOIN jtp_goods_warehouse.tmp_ods_product_all_log t2 ON t1.user_id = t2.user_id
)
INSERT OVERWRITE TABLE jtp_goods_warehouse.dwd_goods_info PARTITION (dt = '${data_date}')
SELECT
    user_id, gender, age, price_preference, interest_tags, sku_id, product_id, sku_name
    , attributes, price, stock, category_id, category_name, parent_category_id, level
    , brand_id, brand_name, brand_desc, review_id, rating, review_text, review_time
    , content_id, content_type, content_title, publish_time, behavior_type, stay_duration
    , traffic_source, keyword, quantity, device_type, operation_type, session_id
    , price_star, competitor_id, competitor_price, keyword_type, product_click_count
    , fan_click_count, collect_guide_count, cart_guide_items, grass_purchase_amount
    , grass_purchase_users, cart_items, pay_buyer_count, pay_items, relation_type, relation_uv
FROM t1
WHERE rk = 1
;
"




/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e  \
"${tmp_ods_product_all_log}${tmp_dim_product_incr}${dwd_goods_info}"